package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.util.DateUtil;
import com.qdlc.p2p.common.util.NumberUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.dao.BondCollectionDao;
import com.qdlc.p2p.dal.dto.BondCollection;
import com.qdlc.p2p.dal.model.BondCollectionModel;

/**
 * 债权待收DAO接口
 * @author zhangyz
 * @version 1.0
 * @since 2014-12-11
 */
@Repository("bondCollectionDao")
public class BondCollectionDaoImpl extends BaseDaoImpl<BondCollection> implements BondCollectionDao {

    @SuppressWarnings("unchecked")
    @Override
    public BondCollection getBondCollectionById(long id) {
        String jpql = "from BondCollection where id = ?1";
        Query query = em.createQuery(jpql);
        query.setParameter(1, id);
        List<BondCollection> list = query.getResultList();
        if (list != null && list.size() >= 0) {
            return (BondCollection) list.get(0);
        } else {
            return null;
        }
    }
    
	@Override
	public BondCollection getCollectionByTenderAndPeriod(long bondTenderId, int period) {
		String jpql = "from BondCollection bc where bc.status=0  and bc.bondTenderId= ?1 and bc.period = ?2";
		Query query =em.createQuery(jpql);
		query.setParameter(1, bondTenderId);
		query.setParameter(2,(byte)period);
		@SuppressWarnings("unchecked")
		List<BondCollection> list = query.getResultList();
		
        if (list != null && list.size() >= 0) {
            return (BondCollection) list.get(0);
        }
        
		return null;
	}    
    
	@Override
	public double getRemainderCapital(long bondTenderId) {
		String sql = "select sum(capital) from BondCollection bc where bc.status=0 and bc.collectionTime >= now() and bc.bondTenderId= ?1 ";
		Query q = em.createQuery(sql).setParameter(1, bondTenderId);
		Object ret = q.getSingleResult();
		if (ret == null)
			return 0;
		return Double.parseDouble(ret.toString());
	}
	
	@Override
	public boolean isLatedByBorrowId(long borrowId) {
		String sql = "select count(bc.id) from BondCollection bc where bc.borrow.id= :borrowId and ((bc.status=0 and bc.collectionTime < now()) or (bc.status=1 and bc.collectionTime < bc.collectionYesTime)) ";
        Query query = em.createQuery(sql);
        query.setParameter("borrowId", borrowId);
		Object ret = query.getSingleResult();
		int count = 0;
		if (ret != null){
			count = Integer.parseInt(ret.toString());
		}
		return (count>0);
	}
	
	@Override
	public List<BondCollection> getBondCollectionList(long repaymentId) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("status", 0);
		param.addParam("borrowRepaymentId", repaymentId);
		return super.findByCriteria(param);
	}

	@Override
	public Object[] getSumBondCollection(long userId) {
		StringBuffer buffer = new StringBuffer("select sum(capital - bondCapital),sum(interest - bondInterest) from BondCollection where status = 0 and user.userId = :userId ");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("userId", userId);
		Object[] object = (Object[])query.getSingleResult();
		return object;
	}

	@Override
	public double sumInterest() {
		StringBuffer jpql = new StringBuffer("SELECT SUM(p1.interest) FROM " + Global.DB_PREFIX + "bond_collection p1 WHERE p1.status = 1");
        Query q = em.createNativeQuery(jpql.toString());
        double interest = 0;
        Object obj = q.getSingleResult();
        if (obj != null) {
            interest = Double.parseDouble(obj.toString());
        }
        return interest;
	}

	@Override
	public List<BondCollectionModel> getCollectionModelListByBondTenderId(long tenderId) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("bondTenderId", tenderId);
		List<BondCollection> collections = this.findByCriteria(param);
		List<BondCollectionModel> bondCollectionModels = new ArrayList<BondCollectionModel>();
		if(collections != null && collections.size() > 0){
			for (int i = 0; i < collections.size(); i++) {
				BondCollectionModel bondCollectionModel = BondCollectionModel.instance(collections.get(i));
				bondCollectionModels.add(bondCollectionModel);
			}
		}
		return bondCollectionModels;
	}

	@Override
	public Object[] getSumBondCollectionByTenderId(long tenderId) {
		StringBuffer buffer = new StringBuffer("select sum(collectionAccount),sum(capital),sum(interest) from BondCollection as bc where bondTenderId = :bondTenderId ");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("bondTenderId", tenderId);
		Object[] object = (Object[])query.getSingleResult();
		return object;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BondCollectionModel> getCollectionModelListByBondId(long bondId) {
		StringBuffer sql = new StringBuffer("select sum(collection_account) as collectionAccount,sum(capital) as capital,sum(interest) as interest,collection_time as collectionTime,period "
				+ "from " + Global.DB_PREFIX + "bond_collection where bond_id = :bondId group by period,collection_time");
		Query query = em.createNativeQuery(sql.toString());
		query.setParameter("bondId", bondId);
		List<Object[]> list = query.getResultList();
		List<BondCollectionModel> list_ = new ArrayList<BondCollectionModel>();
		if(list != null && list.size() > 0){
			for (Object[] obj : list) {
				BondCollectionModel collectionModel = new BondCollectionModel();
				collectionModel.setCollectionAccount(NumberUtil.getDouble(obj[0].toString()));
				collectionModel.setCapital(NumberUtil.getDouble(obj[1].toString()));
				collectionModel.setInterest(NumberUtil.getDouble(obj[2].toString()));
				collectionModel.setCollectionTime(DateUtil.getDateByFullDateStr(obj[3].toString()));
				collectionModel.setPeriod((byte) NumberUtil.getInt(obj[4].toString()));
				list_.add(collectionModel);
			}
		}
		return list_;
	}

	@Override
	public Object[] getSumBondCollectionByBondId(long bondId) {
		StringBuffer buffer = new StringBuffer("select sum(collectionAccount),sum(capital),sum(interest) from BondCollection as bc where bond.id = :bondId ");
		Query query = em.createQuery(buffer.toString());
		query.setParameter("bondId", bondId);
		Object[] object = (Object[])query.getSingleResult();
		return object;
	}
	
}
